package org.vsaulyak.springrssagregator.dao;

import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import javax.inject.Inject;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.vsaulyak.springrssagregator.entity.Chanel;
import org.vsaulyak.springrssagregator.entity.Item;

public class ChanelDaoMySQL extends NamedParameterJdbcDaoSupport
        implements ChanelDao
{
    private static final String PERSIST = 
            "INSERT INTO chanels "
            + "(`cid`, `link`, `title`) VALUES (:cid, :link, :title)";
    private static final String PERSIST_ITEM = "INSERT INTO items (`rank`, `link`, `title`, `chanel_id`) VALUES (:rank, :link, :title, :chanel_id)";
    private static final String GET  = 
            "SELECT "
            + "c.cid AS chanel_cid, c.title AS chanel_title, c.link AS chanel_link, "
            + "i.id AS item_id, i.rank AS item_rank, i.link AS item_link, "
            + "i.title AS item_title "
            + "FROM chanels AS c "
            + "LEFT JOIN items AS i ON (c.id = i.chanel_id) "
            + "WHERE c.cid = :cid";
    private static final String GET_ALL  = 
            "SELECT "
            + "c.cid AS chanel_cid, c.title AS chanel_title, c.link AS chanel_link, "
            + "i.id AS item_id, i.rank AS item_rank, i.link AS item_link, "
            + "i.title AS item_title "
            + "FROM chanels AS c "
            + "LEFT JOIN items AS i ON (c.id = i.chanel_id) "
            + "ORDER BY c.cid";
    private static final String GET_ID = 
            "SELECT `id` FROM chanels WHERE cid = :cid";
    private static final String UPDATE  = 
            "UPDATE chanels SET `cid` = :cid, `link` = :link, "
            + "`title` = :title WHERE cid = :cid";
    private static final String DELETE_ALL_ITEMS = 
            "DELETE c "
            + "FROM items AS i "
            + "LEFT JOIN chanels AS c ON (i.chanel_id = c.id) "
            + "WHERE c.cid = :cid";
    private static final String DELETE = 
            "DELETE FROM chanels WHERE cid = :cid";
    
    @Inject
    private ItemDao itemDao;
    
    @Override
    public void persistChanel(Chanel chanel)
    {
        long chanelId;
        SqlParameterSource params;
        
        try
        {
            params = new MapSqlParameterSource()
                    .addValue("cid", chanel.getId());
            chanelId= namedParameterJdbcTemplate.queryForLong(GET_ID, params);
        }
        catch (EmptyResultDataAccessException e)
        {
            params = new MapSqlParameterSource()
                    .addValue("cid", chanel.getId())
                    .addValue("link", chanel.getLink())
                    .addValue("title", chanel.getTitle());

            KeyHolder key = new GeneratedKeyHolder();
            
            namedParameterJdbcTemplate.update(PERSIST, params, key);
        
            chanelId = key.getKey().longValue();
        }
        
        
        if (0 >= chanelId)
        {
            
        }
        
        for (Item item : chanel.getItems())
        {
            try
            {
                itemDao.persistItem(item, chanelId);
            }
            catch (DuplicateKeyException e)
            {
                //TODO chanel allready in DB
            }
        }
    }
    
    @Override
    public Chanel getChanelById(String chanelId)
    {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("cid", chanelId);
        
        List<Map<String, Object>> result = namedParameterJdbcTemplate.queryForList(GET, params);
        Chanel chanel = new Chanel();
        List<Item> items = new ArrayList<Item>();
        for (Map<String, Object> row : result)
        {
            chanel.setId(chanelId);
            chanel.setLink(row.get("chanel_link").toString());
            chanel.setTitle(row.get("chanel_title").toString());
            Item item = new Item();
            item.setId(Long.parseLong(row.get("item_id").toString()));
            item.setRank(Integer.parseInt(row.get("item_rank").toString()));
            item.setLink(row.get("item_link").toString());
            item.setTitle(row.get("item_title").toString());
            items.add(item);
        }
        chanel.setItems(items);
        return chanel;
    }
    
    @Override
    public void updateChanel(Chanel chanel)
    {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("cid", chanel.getId())
                .addValue("link", chanel.getLink())
                .addValue("title", chanel.getTitle());
        
        namedParameterJdbcTemplate.update(UPDATE, params);
        namedParameterJdbcTemplate.update(DELETE_ALL_ITEMS, params);
        
        params = new MapSqlParameterSource()
                .addValue("cid", chanel.getId());
        
        long chanelId = namedParameterJdbcTemplate.queryForLong(GET_ID, params);
        
        int i = 0;
        Map[] multiinsert = new Map[chanel.getItems().size()];
        for (Item item : chanel.getItems())
        {
            //itemDao.persistItem(item, chanelId);
            Map<String, Object> itemProps = new HashMap<String, Object>();
            itemProps.put("rank", item.getRank());
            itemProps.put("link", item.getLink());
            itemProps.put("title", item.getTitle());
            itemProps.put("chanel_id", chanelId);
            
            multiinsert[i] = itemProps;
            
            i++;
        }
        
        SqlParameterSource[] itemList = 
                SqlParameterSourceUtils.createBatch(multiinsert);
        
        namedParameterJdbcTemplate.batchUpdate(PERSIST_ITEM, itemList);
    }
    
    @Override
    public void deleteChanel(Chanel chanel)
    {
        SqlParameterSource params = new MapSqlParameterSource()
                .addValue("cid", chanel.getId());
        
        namedParameterJdbcTemplate.update(DELETE, params);
    }
    
    @Override
    public List<Chanel> getChanels()
    {
        SqlParameterSource params = new MapSqlParameterSource();
        
        List<Map<String, Object>> result = namedParameterJdbcTemplate.queryForList(GET_ALL, params);
        Chanel chanel;
        List<Item> items = new ArrayList<Item>();
        List<Chanel> chanels = new ArrayList<Chanel>();
        String currentChanelId = "";
        for (Map<String, Object> row : result)
        {
            if (!currentChanelId.equals(row.get("chanel_cid").toString()))
            {
                chanel = new Chanel();
                chanel.setId(row.get("chanel_cid").toString());
                chanel.setLink(row.get("chanel_link").toString());
                chanel.setTitle(row.get("chanel_title").toString());
                items = new ArrayList<Item>();
                chanel.setItems(items);
                chanels.add(chanel);
                currentChanelId = chanel.getId();
            }
            Item item = new Item();
            item.setId(Long.parseLong(row.get("item_id").toString()));
            item.setRank(Integer.parseInt(row.get("item_rank").toString()));
            item.setLink(row.get("item_link").toString());
            item.setTitle(row.get("item_title").toString());
            items.add(item);
        }
        return chanels;
    }
    
    public ItemDao getItemDao()
    {
        return itemDao;
    }
    
    public void setItemDao(ItemDao itemDao)
    {
        this.itemDao = itemDao;
    }
}
